package com.scalar.chiptrack.operations.reports;

import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.PDFGenerator;
import com.scalar.chiptrack.utils.StringUtils;

import java.io.FileWriter;
import java.io.File;
import java.io.IOException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;


/**
 * Author: Varma
 * Date: 28 Oct 2005
 * Description: To generate the Wip Flush PDF, Excel files.
 */
public class WipFlushFileGenerator
{
    /**
     * This method creates the Wip FLush PDF format and writes in to Disk.
     * @param wipInventoryByProduct
     * @return
     */
    public static String getWipFlushAsPDFFile( ArrayList wipInventoryByProduct )
    {
		String fileXMLPath = null;
        String filePDFPath = null;

        String wipFlushAsXML = formatAsXML ( wipInventoryByProduct );

        if ( wipFlushAsXML == null )
        {
            return fileXMLPath;
        }

        FileWriter fWriter = null;
        File wipFlushPDFAttachment = null;
        File wipFlushXML = null;

        try
        {
            // CREATE TEMPORARY ATTACHMENT FILE
            String mailingTempDir = ConfigManager.getMailerTempDir ();
            File tempDir = new File ( mailingTempDir ).getCanonicalFile ();
            tempDir.mkdir ();
            Calendar calendar = new GregorianCalendar();
            String xmlFileName = "WIP_FLUSH_"+(calendar.get(Calendar.MONTH )+1)+"_"+(calendar.get(Calendar.DATE ))+"_"+calendar.get(Calendar.YEAR) +".xml";
            String saAttachmentPDFFileName = "WIP_FLUSH_"+(calendar.get(Calendar.MONTH)+1)+"_"+(calendar.get(Calendar.DATE ))+"_"+calendar.get(Calendar.YEAR) +".pdf";
            saAttachmentPDFFileName = StringUtils.convertLowerCaseToUpperCase( saAttachmentPDFFileName );

            String xsltFileName = ConfigManager.getWipFlushXml2PdfXslFile();
            wipFlushXML = new File ( tempDir.getCanonicalPath (), xmlFileName );


            wipFlushPDFAttachment = new File ( tempDir.getCanonicalPath (), saAttachmentPDFFileName );

            if ( ( wipFlushXML.exists () == true ) || ( wipFlushXML.createNewFile () == true ) )
            {
                fWriter = new FileWriter ( wipFlushXML, false );
                fWriter.write ( wipFlushAsXML );
                fWriter.close ();
                fWriter = null;

                fileXMLPath = wipFlushXML.getCanonicalPath ().toUpperCase ();
                filePDFPath = wipFlushPDFAttachment.getCanonicalPath ().toUpperCase ();

                PDFGenerator.convertXML2PDF ( fileXMLPath, xsltFileName, filePDFPath );
            }
        }
        catch ( Exception e )
        {
            System.out.println ( "Exception while creating Wip Flush PDF file" );
            e.printStackTrace ();
        }
        finally
        {
            if ( fWriter != null )
            {
                try
                {
                    fWriter.close ();
                    fWriter = null;
                }
                catch ( IOException ignore )
                {
                }
            }
        }

        return filePDFPath;
    }
    /**
     * This method is to generate the XML file of WIP FLush.
     * @param wipArrayList
     * @return
     */
    public static String formatAsXML( ArrayList wipArrayList )
    {
        StringBuffer buf 		= new StringBuffer ( 2098 );
        int fabTotalQty         = 0;
        int waferStoreTotalQty  = 0;
        int sortTotalQty        = 0;
        int dieBankTotalQty     = 0;
        int assemblyTotalQty    = 0;
        int testTotalQty        = 0;
        int inTransitTotalQty   = 0;
        int classStoreTotalQty  = 0;
        int programReleaseQty   = 0;
        int fgTotalQty          = 0;
        int backlogTotalQty     = 0;

        if( wipArrayList != null )
        {
            buf.append ( "<?xml version='1.0' encoding='UTF-8'?>\n" );
            buf.append ( "<wipFlush> \n" );
            Calendar calendar = new GregorianCalendar();
            buf.append ( "<currentDate><![CDATA[" ) .append ( StringUtils.formatDateToString( calendar.getTime() ) ) . append( "]]></currentDate>\n" );
            buf.append ( "<baseProducts> \n" );
            for( int i=0; i< wipArrayList.size(); i++ )
            {
                WipFlush baseProduct = (WipFlush)wipArrayList.get(i);
                buf.append ( "<group>\n" );
                buf.append ( "<baseProduct> \n" );
                buf.append ( "<partNumber><![CDATA[" ) .append ( baseProduct.getPartNumber() ) . append( "]]></partNumber>\n" );
                buf.append ( "<formattedFabQty><![CDATA[" ) .append ( baseProduct.getFormattedFabQty() ) . append( "]]></formattedFabQty>\n" );
                buf.append ( "<formattedWaferStoreQty><![CDATA[" ) .append ( baseProduct.getFormattedWaferStoreQty() ) . append( "]]></formattedWaferStoreQty>\n" );
                buf.append ( "<formattedSortQty><![CDATA[" ) .append ( baseProduct.getFormattedSortQty() ) . append( "]]></formattedSortQty>\n" );
                buf.append ( "<formattedDieBankQty><![CDATA[" ) .append ( baseProduct.getFormattedDieBankQty() ) . append( "]]></formattedDieBankQty>\n" );
                buf.append ( "<formattedAssemblyQty><![CDATA[" ) .append ( baseProduct.getFormattedAssemblyQty() ) . append( "]]></formattedAssemblyQty>\n" );
                buf.append ( "<formattedTestQty><![CDATA[" ) .append ( baseProduct.getFormattedTestQty() ) . append( "]]></formattedTestQty>\n" );
                buf.append ( "<formattedInTransitQty><![CDATA[" ) .append ( baseProduct.getFormattedInTransitQty() ) . append( "]]></formattedInTransitQty>\n" );
                buf.append ( "<formattedClassStoreQty><![CDATA[" ) .append ( baseProduct.getFormattedClassStoreQty() ) . append( "]]></formattedClassStoreQty>\n" );
                buf.append ( "<formattedProgramReleaseQty><![CDATA[" ) .append ( baseProduct.getFormattedProgramReleaseQty() ) . append( "]]></formattedProgramReleaseQty>\n" );
                buf.append ( "<formattedFgQty><![CDATA[" ) .append ( baseProduct.getFormattedFgQty() ) . append( "]]></formattedFgQty>\n" );
                buf.append ( "<formattedBacklogQty><![CDATA[" ) .append ( baseProduct.getFormattedBacklogQty() ) . append( "]]></formattedBacklogQty>\n" );

                HashMap childProducts = baseProduct.getChildMap();
                if( childProducts != null )
                {

                    Iterator it = childProducts.keySet().iterator();
                    buf.append ( "<childProducts> \n" );
                    while( it.hasNext() )
                    {
                        WipFlush product = (WipFlush)childProducts.get( it.next() );

                        fabTotalQty         += product.getFabQty() ;
                        waferStoreTotalQty  += product.getWaferStoreQty();
                        sortTotalQty        += product.getSortQty();
                        dieBankTotalQty     += product.getDieBankQty();
                        assemblyTotalQty    += product.getAssemblyQty();
                        testTotalQty        += product.getTestQty();
                        inTransitTotalQty   += product.getInTransitQty();
                        classStoreTotalQty  += product.getClassStoreQty();
                        programReleaseQty   += product.getProgramReleaseQty();
                        fgTotalQty          += product.getFgQty();
                        backlogTotalQty     += product.getBacklogQty();

                        buf.append ( "<childProduct> \n" );
                        buf.append ( "<partNumber><![CDATA[" ) .append ( product.getPartNumber() ) . append( "]]></partNumber>\n" );
                        buf.append ( "<formattedFabQty><![CDATA[" ) .append ( product.getFormattedFabQty() ) . append( "]]></formattedFabQty>\n" );
                        buf.append ( "<formattedWaferStoreQty><![CDATA[" ) .append ( product.getFormattedWaferStoreQty() ) . append( "]]></formattedWaferStoreQty>\n" );
                        buf.append ( "<formattedSortQty><![CDATA[" ) .append ( product.getFormattedSortQty() ) . append( "]]></formattedSortQty>\n" );
                        buf.append ( "<formattedDieBankQty><![CDATA[" ) .append ( product.getFormattedDieBankQty() ) . append( "]]></formattedDieBankQty>\n" );
                        buf.append ( "<formattedAssemblyQty><![CDATA[" ) .append ( product.getFormattedAssemblyQty() ) . append( "]]></formattedAssemblyQty>\n" );
                        buf.append ( "<formattedTestQty><![CDATA[" ) .append ( product.getFormattedTestQty() ) . append( "]]></formattedTestQty>\n" );
                        buf.append ( "<formattedInTransitQty><![CDATA[" ) .append ( product.getFormattedInTransitQty() ) . append( "]]></formattedInTransitQty>\n" );
                        buf.append ( "<formattedClassStoreQty><![CDATA[" ) .append ( product.getFormattedClassStoreQty() ) . append( "]]></formattedClassStoreQty>\n" );
                        buf.append ( "<formattedProgramReleaseQty><![CDATA[" ) .append ( product.getFormattedProgramReleaseQty() ) . append( "]]></formattedProgramReleaseQty>\n" );
                        buf.append ( "<formattedFgQty><![CDATA[" ) .append ( product.getFormattedFgQty() ) . append( "]]></formattedFgQty>\n" );
                        buf.append ( "<formattedBacklogQty><![CDATA[" ) .append ( product.getFormattedBacklogQty() ) . append( "]]></formattedBacklogQty>\n" );
                        buf.append ( "</childProduct> \n" );

                    }
                    buf.append ( "</childProducts> \n" );
                }
                buf.append ( "</baseProduct> \n" );
                buf.append ( "</group>\n" );
            }
            buf.append ( "</baseProducts> \n" );
            buf.append ( "<fabTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(fabTotalQty) ) . append( "]]></fabTotalQty>\n" );
            buf.append ( "<waferStoreTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(waferStoreTotalQty) ) . append( "]]></waferStoreTotalQty>\n" );
            buf.append ( "<sortTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(sortTotalQty) ) . append( "]]></sortTotalQty>\n" );
            buf.append ( "<dieBankTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(dieBankTotalQty) ) . append( "]]></dieBankTotalQty>\n" );
            buf.append ( "<assemblyTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(assemblyTotalQty) ) . append( "]]></assemblyTotalQty>\n" );
            buf.append ( "<testTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(testTotalQty) ) . append( "]]></testTotalQty>\n" );
            buf.append ( "<inTransitTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(inTransitTotalQty) ) . append( "]]></inTransitTotalQty>\n" );
            buf.append ( "<classStoreTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(classStoreTotalQty) ) . append( "]]></classStoreTotalQty>\n" );
            buf.append ( "<programReleaseQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(programReleaseQty) ) . append( "]]></programReleaseQty>\n" );
            buf.append ( "<fgTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(fgTotalQty) ) . append( "]]></fgTotalQty>\n" );
            buf.append ( "<backlogTotalQty><![CDATA[" ) .append ( StringUtils.getUSNumberFormat(backlogTotalQty) ) . append( "]]></backlogTotalQty>\n" );

            buf.append ( "</wipFlush> \n" );
        }
        return buf.toString();

    }
    /**
     * This Method is to generate the Wip FLush Excel file and writes in to Disk.
     * @param wipInventoryByProduct
     * @return
     */
    public static String getWipFlushAsExcelFile( ArrayList wipInventoryByProduct )
    {
        Workbook workbook = null;
        try
        {
            workbook = getWorkbookForWipFlushByProduct( wipInventoryByProduct );
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
        FileOutputStream fileOut;
        String excelFilePath = null;
        try
        {
            excelFilePath =  ConfigManager.getWipFlushFilePathCmd();
            boolean exists = ( new File( excelFilePath ) ).exists();
            if ( !exists )
            {
                File file = new File( excelFilePath );
                file.mkdirs();
            }
            Calendar calendar = new GregorianCalendar();
            String fileName = "WIP_FLUSH_"+( calendar.get(Calendar.MONTH )+1 )+"_"+( calendar.get( Calendar.DATE ) )+"_"+ calendar.get( Calendar.YEAR );
            fileOut = new FileOutputStream( excelFilePath + "/" + fileName );
            if ( workbook != null )
            {
                try
                {
                    workbook.write( fileOut );
                }
                catch ( Exception e )
                {
                    e.printStackTrace();
                }
            }
            fileOut.close();
            excelFilePath = excelFilePath + "/" + fileName;
        }
        catch ( Exception e)
        {
            e.printStackTrace();
        }

         return excelFilePath;

     }
    /**
     * This method is to generate the Wip Flush Work Book.
     * @param baseProductList
     * @return
     */
    public static Workbook getWorkbookForWipFlushByProduct ( ArrayList baseProductList )
    {
        Workbook workbook   = null;
        Sheet sheet       = null;
        int baseProductListSize = ( baseProductList == null ) ? 0 : baseProductList.size();
        if ( baseProductListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = workbook.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush By Product" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex() );

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex() );

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex() );
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)10, titleStyle );
            Calendar calendar = new GregorianCalendar();
            setCellValue ( cell, ch.createRichTextString("Wip Flush Inventory Report-"+StringUtils.formatDateToString( calendar.getTime() ) ), titleStyle );

            //SETTING HEADINGS
            String headers[] = { "Part Number", "Fab", "Wafer Store", "Sort", "DieBank", "Assembly", "Test", "InTransit", "Class Store", "Prog Release", "Finished Goods", "Backlogs"};
            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( i );
                setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                if( i == 0 )  //means Part number
                {
                    sheet.setColumnWidth( i, (short) ( ( ( width + 6 ) * 256 ) ) );
                }
                else
                {
                    sheet.setColumnWidth( i, (short) ( ( ( width ) * 256 ) ) );
                }


            }

            int fabTotalQty = 0;
            int waferStoreTotalQty = 0;
            int sortTotalQty = 0;
            int dieBankTotalQty = 0;
            int assemblyTotalQty = 0;
            int testTotalQty = 0;
            int classStoreTotalQty = 0;
            int fgTotalQty = 0;
            int backlogTotalQty = 0;
            int inTransitTotalQty = 0;
            int programReleaseQty = 0;
            int nextRowNumber   = 3;
            for ( int i = 0; i < baseProductListSize; i++ )
            {
                WipFlush baseProduct    =  ( WipFlush )baseProductList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                setCellValue( row, 0, baseProduct.getPartNumber(), baseProductStyle  );
                setCellValue( row, 1, baseProduct.getFabQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 2, baseProduct.getWaferStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC );
                setCellValue( row, 3, baseProduct.getSortQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 4, baseProduct.getDieBankQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 5, baseProduct.getAssemblyQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 6, baseProduct.getTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 7, baseProduct.getInTransitQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 8, baseProduct.getClassStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 9, baseProduct.getProgramReleaseQty(), baseProductValueItalicStyle, Cell.CELL_TYPE_NUMERIC );
                setCellValue( row, 10, baseProduct.getFgQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, 11, baseProduct.getBacklogQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );

                HashMap childMap    = baseProduct.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlush product    = ( WipFlush )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    setCellValue( row, 0, product.getPartNumber(), productStyle  );
                    setCellValue( row, 1, product.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 2, product.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 3, product.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 4, product.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 5, product.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 6, product.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 7, product.getInTransitQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 8, product.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 9, product.getProgramReleaseQty(), productValueItalicStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 10, product.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, 11, product.getBacklogQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                    fabTotalQty         += product.getFabQty() ;
                    waferStoreTotalQty  += product.getWaferStoreQty();
                    sortTotalQty        += product.getSortQty();
                    dieBankTotalQty     += product.getDieBankQty();
                    assemblyTotalQty    += product.getAssemblyQty();
                    testTotalQty        += product.getTestQty();
                    classStoreTotalQty  += product.getClassStoreQty();
                    fgTotalQty          += product.getFgQty();
                    backlogTotalQty     += product.getBacklogQty();
                    inTransitTotalQty   += product.getInTransitQty();
                    programReleaseQty   += product.getProgramReleaseQty();
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            setCellValue( row, 0, "Grand Total", headerStyle  );
            setCellValue( row, 1, fabTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 2, waferStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 3, sortTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 4, dieBankTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 5, assemblyTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 6, testTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 7, inTransitTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 8, classStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 9, programReleaseQty, headerValueItalicStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 10, fgTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, 11, backlogTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
        }
        return workbook;
    }

    private static Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }

    private static void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    private static void setCellValue ( Row row, int cellNo, String value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    private static void setCellValue ( Row row, int cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }

     private static Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }


}